﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Web;

namespace Jxym.DBUtility
{
    public class MsSqlDal<T> : IDBHelper<T>
    {
        private const string SP_PAGER = "[pager_t]";

        private string _connectionString;
        private string _connectionStringName;
        private string _primaryKeyName;

        public MsSqlDal()
        {
            this._primaryKeyName = "ID";
            this._connectionStringName = "SqlConnectionString";
            this._connectionString = "";
            this._connectionString = ConfigurationManager.ConnectionStrings[this._connectionStringName].ConnectionString;
        }

        public MsSqlDal(string primaryKeyName)
        {
            this._primaryKeyName = "ID";
            this._connectionStringName = "SqlConnectionString";
            this._connectionString = "";
            this._primaryKeyName = primaryKeyName;
            this._connectionString = ConfigurationManager.ConnectionStrings[this._connectionStringName].ConnectionString;
        }

        public MsSqlDal(string primaryKeyName, string connectionStringName)
        {
            this._primaryKeyName = "ID";
            this._connectionStringName = "SqlConnectionString";
            this._connectionString = "";
            this._primaryKeyName = primaryKeyName;
            this._connectionStringName = connectionStringName;
            this._connectionString = ConfigurationManager.ConnectionStrings[this._connectionStringName].ConnectionString;
        }

        public object Add(SqlParameter[] parameters)
        {
            string comucls = "";
            string comuclsValue = "";
            Type type = typeof(T);
            foreach (SqlParameter pi in parameters)
            {
                if (pi.ParameterName != ("@" + this._primaryKeyName))
                {
                    comucls = comucls + pi.ParameterName.TrimStart(new char[] { '@' }) + ",";
                    comuclsValue = comuclsValue + pi.ParameterName + ",";
                }
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            comuclsValue = comuclsValue.TrimEnd(new char[] { ',' });
            StringBuilder sb = new StringBuilder("INSERT INTO " + type.Name + "(" + comucls + ") VALUES(" + comuclsValue + ") SELECT @@IDENTITY AS 'IDENTITY'");
            return JxymSqlHelper.ExecuteScalar(this._connectionString, CommandType.Text, sb.ToString(), parameters);
        }

        public int Delete(object primaryKeyValue)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@" + this._primaryKeyName, primaryKeyValue) };
            Type type = typeof(T);
            StringBuilder sbCount = new StringBuilder("DELETE FROM " + type.Name + " WHERE (" + this._primaryKeyName + "=@" + this._primaryKeyName + ")");
            return JxymSqlHelper.ExecuteNonQuery(this._connectionString, CommandType.Text, sbCount.ToString(), parameters);
        }

        public int Delete(string strWhere, SqlParameter[] parameters)
        {
            Type type = typeof(T);

            StringBuilder sb = new StringBuilder("DELETE FROM " + type.Name);

            if (strWhere != "")
            {
                sb.Append(" WHERE (" + strWhere + ")");
            }
            return JxymSqlHelper.ExecuteNonQuery(this._connectionString, CommandType.Text, sb.ToString(), parameters);
        }

        public object[] Deletes(object[] primaryKeyValues)
        {
            ArrayList list = new ArrayList();

            foreach (string value in primaryKeyValues)
            {
                if (this.Delete(value) > 0)
                {
                    list.Add(value);
                }
            }

            object[] counts = new object[list.Count];

            for (int i = 0; i < counts.Length; i++)
            {
                counts[i] = list[i];
            }
            return counts;
        }

        public IList<T> GetList(string sortField)
        {
            return this.GetList("", null, sortField, 0);
        }

        public IList<T> GetList(string strWhere, SqlParameter[] parameters, string sortField, int topN)
        {
            string comucls = "";
            string topStr = "";
            IList<T> list = new List<T>();
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            T t = default(T);
            foreach (PropertyInfo pi in properties)
            {
                comucls = comucls + pi.Name + ",";
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            if (topN != 0)
            {
                topStr = " TOP " + topN;
            }
            StringBuilder sb = new StringBuilder("SELECT" + topStr + " " + comucls + " FROM " + type.Name);
            if (strWhere != "")
            {
                sb.Append(" WHERE (" + strWhere + ")");
            }
            if (sortField != "")
            {
                sb.Append(" ORDER BY " + sortField);
            }
            using (DataTable dt = JxymSqlHelper.ExecuteTable(this._connectionString, CommandType.Text, sb.ToString(), parameters))
            {
                foreach (DataRow dr in dt.Rows)
                {
                    t = (T)Activator.CreateInstance(type);
                    for (int i = 0; i < properties.Length; i++)
                    {
                        object v = dr[i];
                        properties[i].SetValue(t, v, null);
                    }
                    list.Add(t);
                }
            }
            return list;
        }

        public T GetModel(object primaryKeyValue)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@" + this._primaryKeyName, primaryKeyValue) };
            return this.GetModel(this._primaryKeyName + "=@" + this._primaryKeyName, parameters);
        }

        public T GetModel(string strWhere, SqlParameter[] parameters)
        {
            string comucls = "";
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            T t = default(T);
            foreach (PropertyInfo pi in properties)
            {
                comucls = comucls + pi.Name + ",";
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            StringBuilder sb = new StringBuilder("SELECT " + comucls + " FROM " + type.Name);
            if (strWhere != "")
            {
                sb.Append(" WHERE (" + strWhere + ")");
            }
            using (DataTable dt = JxymSqlHelper.ExecuteTable(this._connectionString, CommandType.Text, sb.ToString(), parameters))
            {
                if (dt.Rows.Count <= 0)
                {
                    return t;
                }
                t = (T)Activator.CreateInstance(type);
                for (int i = 0; i < properties.Length; i++)
                {
                    properties[i].SetValue(t, (dt.Rows[0][i] == DBNull.Value) ? "" : dt.Rows[0][i], null);
                }
            }
            return t;
        }

        public int GetPageCount(string strWhere, SqlParameter[] parameters)
        {
            Type type = typeof(T);
            StringBuilder sbCount = new StringBuilder("SELECT COUNT(" + this._primaryKeyName + ") FROM " + type.Name);
            if (strWhere != "")
            {
                sbCount.Append(" WHERE (" + strWhere + ")");
            }
            return Convert.ToInt32(JxymSqlHelper.ExecuteScalar(this._connectionString, CommandType.Text, sbCount.ToString(), parameters));
        }

        public IList<T> GetPageList(string strWhere, SqlParameter[] parameters, string sortField, int startIndex, int limit)
        {
            string comucls = "";
            IList<T> list = new List<T>();
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            T t = default(T);
            foreach (PropertyInfo pi in properties)
            {
                comucls = comucls + pi.Name + ",";
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            string mywhere = "";
            if (strWhere != "")
            {
                mywhere = " WHERE (" + strWhere + ")";
            }
            string sort = this._primaryKeyName + " DESC";
            if (sortField != "")
            {
                sort = sortField;
            }

            StringBuilder sb = new StringBuilder(string.Concat(new object[] { 
                "SELECT TOP ", limit, " ", comucls, 
                " FROM ", type.Name, 
                " WHERE (", this._primaryKeyName, " <= (SELECT ISNULL(MIN(", this._primaryKeyName, "),0) FROM(SELECT TOP (", limit, "*", startIndex / limit, "+1) ", this._primaryKeyName, 
                " FROM ", type.Name, " ", mywhere, " ORDER BY ",sort ,") A))"
             }));
            if (strWhere != "")
            {
                sb.Append(" AND (" + strWhere + ")");
            }
            sb.Append(" ORDER BY " + sort);
            using (DataTable dt = JxymSqlHelper.ExecuteTable(this._connectionString, CommandType.Text, sb.ToString(), parameters))
            {
                foreach (DataRow dr in dt.Rows)
                {
                    t = (T)Activator.CreateInstance(type);
                    for (int i = 0; i < properties.Length; i++)
                    {
                        properties[i].SetValue(t, (dr[i] == DBNull.Value) ? "" : dr[i], null);
                    }
                    list.Add(t);
                }
            }
            return list;
        }

        public SqlParameter[] GetParameters(HttpRequest reuqest)
        {
            int i;
            PropertyInfo[] list = typeof(T).GetProperties();
            ArrayList pNameList = new ArrayList();
            ArrayList pValueList = new ArrayList();
            for (i = 0; i < list.Length; i++)
            {
                string name = list[i].Name;
                string value = null;

                if ((reuqest.Params[name] != null) && (reuqest.Params[name] != ""))
                {
                    value = reuqest.Params[name];

                    if (value != null)
                    {
                        pNameList.Add("@" + name);

                        if (value == "on")
                        {
                            pValueList.Add(true);
                        }
                        else if (value == "off")
                        {
                            pValueList.Add(false);
                        }
                        else
                        {
                            pValueList.Add(value);
                        }
                    }
                }
            }
            SqlParameter[] parameters = new SqlParameter[pNameList.Count];
            for (i = 0; i < pNameList.Count; i++)
            {
                parameters[i] = new SqlParameter(pNameList[i].ToString(), pValueList[i]);
            }
            return parameters;
        }

        public bool IsExist(string whereStr, SqlParameter[] parameters)
        {
            Type type = typeof(T);
            StringBuilder sbCount = new StringBuilder("SELECT COUNT(" + this._primaryKeyName + ") FROM " + type.Name + " WHERE (" + whereStr + ")");
            return (Convert.ToInt32(JxymSqlHelper.ExecuteScalar(this._connectionString, CommandType.Text, sbCount.ToString(), parameters)) > 0);
        }

        public int Updata(SqlParameter[] parameters)
        {
            string comucls = "";
            Type type = typeof(T);
            foreach (SqlParameter pi in parameters)
            {
                if (pi.ParameterName != ("@" + this._primaryKeyName))
                {
                    comucls += pi.ParameterName.TrimStart(new char[] { '@' }) + "=" + pi.ParameterName + ",";
                }
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            StringBuilder sb = new StringBuilder("UPDATE " + type.Name + " SET " + comucls + " WHERE(" + this._primaryKeyName + "=@" + this._primaryKeyName + ")");
            return JxymSqlHelper.ExecuteNonQuery(this._connectionString, CommandType.Text, sb.ToString(), parameters);
        }

        public int Updata(string whereStr, SqlParameter[] parameters)
        {
            string comucls = "";
            Type type = typeof(T);
            foreach (SqlParameter pi in parameters)
            {
                if (pi.ParameterName != ("@" + this._primaryKeyName))
                {
                    comucls += pi.ParameterName.TrimStart(new char[] { '@' }) + "=" + pi.ParameterName + ",";
                }
            }
            comucls = comucls.TrimEnd(new char[] { ',' });
            StringBuilder sb = new StringBuilder("UPDATE " + type.Name + " SET " + comucls + " WHERE " + whereStr);
            return JxymSqlHelper.ExecuteNonQuery(this._connectionString, CommandType.Text, sb.ToString(), parameters);
        }

        public string ConnectionStringName
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                this._connectionString = value;
            }
        }

        public string PrimaryKeyName
        {
            get
            {
                return this._primaryKeyName;
            }
            set
            {
                this._primaryKeyName = value;
            }
        }

        public IList<T> GetPageList(string strWhere, SqlParameter[] parameters, int startIndex, int limit)
        {
            return GetPageList(strWhere, parameters, "", startIndex, limit);
        }

        public IList<T> GetPageList(string strWhere, string sortField, bool isDesc, int startIndex, int limit)
        {
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            T t = default(T);

            IList<T> list = new List<T>();

            string comucls = ""; foreach (PropertyInfo pi in properties) { comucls += pi.Name + ","; } comucls = comucls.TrimEnd(new char[] { ',' });

            string mywhere = ""; if (strWhere != "") { mywhere = strWhere; }

            string sort = this._primaryKeyName; if (sortField != "") { sort = sortField; }

            int orderType = 1; if (isDesc) orderType = 1; else orderType = 0;

            int pageIndex = 1; if (startIndex % limit == 0) { pageIndex = startIndex / limit; } if (startIndex % limit > 0) { pageIndex = startIndex / limit + 1; }

            SqlParameter[] parames = new SqlParameter[] { 
                new SqlParameter("@tblName",type.Name),
                new SqlParameter("@strGetFields",comucls),
                new SqlParameter("@fldName",sort),
                new SqlParameter("@PageSize",limit),
                new SqlParameter("@PageIndex",pageIndex),
                new SqlParameter("@doCount",0),
                new SqlParameter("@OrderType",orderType),
                new SqlParameter("@strWhere",mywhere)
            };

            using (SqlDataReader sdr = JxymSqlHelper.ExecuteReader(this._connectionString, CommandType.StoredProcedure, SP_PAGER, parames))
            {
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        t = (T)Activator.CreateInstance(type);

                        Utility.ModelValue(t, sdr);

                        list.Add(t);
                    }
                }
            }
            return list;
        }

        public IList<T> GetPageList(string strWhere, string sortField, int sortType, int startIndex, int limit)
        {
            Type type = typeof(T);

            PropertyInfo[] properties = type.GetProperties();

            T t = default(T);

            IList<T> list = new List<T>();

            string comucls = ""; foreach (PropertyInfo pi in properties) { comucls += pi.Name + ","; } comucls = comucls.TrimEnd(new char[] { ',' });

            string mywhere = ""; if (strWhere != "") { mywhere = strWhere; }

            string sort = this._primaryKeyName; if (sortField != "") { sort = sortField; }

            int pageIndex = 1; if (startIndex % limit == 0) { pageIndex = startIndex / limit; } if (startIndex % limit > 0) { pageIndex = startIndex / limit + 1; }

            SqlParameter[] parames = new SqlParameter[] { 
                new SqlParameter("@TableName",type.Name),       //表名
                new SqlParameter("@FieldList",comucls),         //显示列名，如果是全部字段则为*
                new SqlParameter("@PrimaryKey",_primaryKeyName),//单一主键或唯一值键
                new SqlParameter("@Where",mywhere),             //查询条件 不含'where'字符，如id>10 and len(userid)>9
                new SqlParameter("@Order",sort),                //排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
                new SqlParameter("@SortType",sortType),         //排序规则 1:正序asc 2:倒序desc 3:多列排序方法
                new SqlParameter("@RecorderCount","0"),           //记录总数 0:会返回总记录
                new SqlParameter("@PageSize",limit),            //每页输出的记录数
                new SqlParameter("@PageIndex",pageIndex),       //当前页数
                new SqlParameter("@TotalCount","0"),              //记返回总记录
                new SqlParameter("@TotalPageCount","0")           //返回总页数
            };

            using (SqlDataReader sdr = JxymSqlHelper.ExecuteReader(this._connectionString, CommandType.StoredProcedure, SP_PAGER, parames))
            {
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        t = (T)Activator.CreateInstance(type);

                        Utility.ModelValue(t, sdr);

                        list.Add(t);
                    }
                }
            }
            return list;
        }
    }
}

